﻿using System;
using System.Collections;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;

namespace DAO
{
    public static class BDServices
    {
        private static SqlConnection conexion;
        private static SqlTransaction transaccion;
        private static SqlCommand cmd;

        public static int InsertCommand(string tabla, Dictionary<string, object> diccionario) 
        {
            int id = 0;

            try
            {
                ConfigurarCommand(true);
                cmd.CommandText = "INSERT INTO " + tabla + "(" + ObtenerCampos(diccionario) + ") VALUES (" + ObtenerValores(diccionario) + ")";

                SetearParametros(cmd, diccionario);

                id = EjecutarCommand(true, tabla, true, true, true);
            }
            catch (Exception ex)
            {
                if (conexion.State == ConnectionState.Open)
                {
                    cmd.Transaction.Rollback();
                    conexion.Close();
                }
                throw new Exception("Error al intentar grabar. " + ex.Message);
            }

            return id;
        }

        public static int InsertCommandMaestroDetalles(string tablaMaestro, Dictionary<string, object> diccionarioMaestro, string tablaDetalle, List<Dictionary<string, object>> diccionarioDetalle, string claveForanea)
        {
            int id = 0;

            try
            {
                ConfigurarCommand(true);
                cmd.CommandText = "INSERT INTO " + tablaMaestro + "(" + ObtenerCampos(diccionarioMaestro) + ") VALUES (" + ObtenerValores(diccionarioMaestro) + ")";

                SetearParametros(cmd, diccionarioMaestro);

                id = EjecutarCommand(true, tablaMaestro, true, false, false);
                foreach (Dictionary<string, object> dato in diccionarioDetalle)
                    dato[claveForanea] = id;
                for (int i = 0; i < diccionarioDetalle.Count; i++)
                {
                    cmd.CommandText = "INSERT INTO " + tablaDetalle + "(" + ObtenerCampos(diccionarioDetalle[i]) + ") VALUES (" + ObtenerValores(diccionarioDetalle[i]) + ")";
                    SetearParametros(cmd, diccionarioDetalle[i]);
                    EjecutarCommand(true, tablaDetalle, true, i == diccionarioDetalle.Count - 1, i == diccionarioDetalle.Count - 1);
                    cmd.Parameters.Clear();
                }
            }
            catch (Exception ex)
            {
                if (conexion.State == ConnectionState.Open)
                {
                    cmd.Transaction.Rollback();
                    conexion.Close();
                }
                throw new Exception("Error al intentar grabar. " + ex.Message);
            }

            return id;
        }
       
        public static void DeleteCommand(string tabla, int id)
        {
            try
            {
                ConfigurarCommand(true);

                cmd.CommandText = "DELETE FROM " + tabla + " WHERE id = @id ";
                cmd.Parameters.AddWithValue("@id", id);

                EjecutarCommand(true, tabla, false, true, true);
            }
            catch (Exception ex)
            {
                if (conexion.State == ConnectionState.Open)
                {
                    cmd.Transaction.Rollback();
                    conexion.Close();
                }
                throw new Exception("Error al intentar eliminar. " + ex.Message);
            }
        }

        public static void UpdateCommand(string tabla, Dictionary<string, object> diccionario, int id)
        {
            try
            {
                ConfigurarCommand(true);

                cmd.CommandText = "UPDATE " + tabla + " SET " + ObtenerCamposValores(diccionario) + " WHERE id = @id";
                SetearParametros(cmd, diccionario);                
                cmd.Parameters.AddWithValue("@id", id);

                EjecutarCommand(true, tabla, false, true, true);
            }
            catch (Exception ex)
            {
                if (conexion.State == ConnectionState.Open)
                {
                    cmd.Transaction.Rollback();
                    conexion.Close();
                }
                throw new Exception("Error al intentar actualizar. " + ex.Message);
            }
        }

        public static void UpdateCommandMaestroDetalle(string tablaMaestro, Dictionary<string, object> diccionarioMaestro, string tablaDetalle, List<Dictionary<string, object>> diccionarioDetalle, string claveForenea, int id)
        {
            try
            {
                ConfigurarCommand(true);

                cmd.CommandText = "UPDATE " + tablaMaestro + " SET " + ObtenerCamposValores(diccionarioMaestro) + " WHERE id = @id";
                SetearParametros(cmd, diccionarioMaestro);
                cmd.Parameters.AddWithValue("@id", id);

                EjecutarCommand(true, tablaMaestro, false, false, false);
                foreach (Dictionary<string, object> dato in diccionarioDetalle)
                    dato[claveForenea] = id;
                for (int i = 0; i < diccionarioDetalle.Count; i++)
                {
                    cmd.CommandText = "UPDATE " + tablaDetalle + "(" + ObtenerCamposValores(diccionarioDetalle[i]) + " WHERE " + claveForenea + " = @" + claveForenea;
                    SetearParametros(cmd, diccionarioDetalle[i]);
                    EjecutarCommand(true, tablaDetalle, true, i == diccionarioDetalle.Count - 1, i == diccionarioDetalle.Count - 1);
                    cmd.Parameters.Clear();
                }

            }
            catch (Exception ex)
            {
                if (conexion.State == ConnectionState.Open)
                {
                    cmd.Transaction.Rollback();
                    conexion.Close();
                }
                throw new Exception("Error al intentar actualizar. " + ex.Message);
            }
        }

        public static SqlDataReader SelectCommand(string tabla, Dictionary<string, object> diccionario, int id)
        {
            SqlDataReader dataReader = null;

            try
            {
                ConfigurarCommand(false);

                cmd.CommandText = "SELECT * FROM " + tabla + " WHERE id = @id";
                cmd.Parameters.AddWithValue("@id", id);

                dataReader = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                if (conexion.State == ConnectionState.Open)
                {
                    cmd.Transaction.Rollback();
                    conexion.Close();
                }
                throw new Exception("Error al intentar seleccionar. " + ex.Message);
            }

            return dataReader;
        }

        public static SqlDataReader SelectAllCommand(string tabla)
        {
            SqlDataReader dataReader = null;
            try
            {
                ConfigurarCommand(false);

                cmd.CommandText = "SELECT * FROM " + tabla;                
                dataReader = cmd.ExecuteReader();
                //conexion.Close();
            }
            catch (Exception ex)
            {
                if (conexion.State == ConnectionState.Open)
                {                                      
                    conexion.Close();
                }
                throw new Exception("Error al intentar seleccionar todas las entidades. " + ex.Message);
            }

            return dataReader;
        }

        private static string ObtenerCamposValores(Dictionary<string, object> diccionario)
        {
            StringBuilder campoValor = new StringBuilder();

            foreach (var item in diccionario)
            {
                campoValor.Append(item.Key + " = " + "@" + item.Key + ",");
            }

            return campoValor.ToString().Substring(0, campoValor.ToString().Length - 1);
        }

        private static void ConfigurarCommand(bool permiteTransaccion)
        {
            conexion = DALSQLServer.Instancia().Conexion;

            if (conexion.State == ConnectionState.Closed)
            {
                conexion.Open();
            }

            cmd = new SqlCommand();

            if (permiteTransaccion)
            {
                transaccion = conexion.BeginTransaction();
                cmd.Transaction = transaccion;
            }
                                   
            cmd.Connection = conexion;
            cmd.CommandType = CommandType.Text;
        }

        private static int EjecutarCommand(bool permiteTransaccion, string tabla, bool obtenerId, bool cerrarTransaccion, bool cerrarConexion)
        {
            int id = 0;
            
            cmd.ExecuteNonQuery();

            if (permiteTransaccion)
            {
                if (obtenerId)
                    id = ObtenerId();
                if (cerrarTransaccion)
                    cmd.Transaction.Commit();
            }
            if (cerrarConexion)
                conexion.Close();

            return id;
        }        

        private static void SetearParametros(SqlCommand cmd, Dictionary<string, object> diccionario)
        {
            foreach (var item in diccionario)
            {
                cmd.Parameters.AddWithValue("@" + item.Key.ToString(), item.Value);
            }
        }

        private static string ObtenerValores(Dictionary<string, object> diccionario)
        {
            StringBuilder campos = new StringBuilder();

            foreach (var item in diccionario)
            {
                campos.Append("@" + item.Key.ToString() + ",");
            }

            return campos.ToString().Substring(0, campos.ToString().Length - 1);
        }

        private static string ObtenerCampos(Dictionary<string, object> diccionario)
        {
            StringBuilder campos = new StringBuilder();

            foreach (var item in diccionario)
            {
                campos.Append(item.Key.ToString() + ",");
            }
           
            return campos.ToString().Substring(0, campos.ToString().Length-1);
        }

        private static int ObtenerId()
        {
            int id = 0;

            SqlCommand cmd2 = new SqlCommand("SELECT @@Identity", conexion);
            cmd2.Transaction = cmd.Transaction;
            id = Convert.ToInt32(cmd2.ExecuteScalar());

            return id;
        }
    }
}
